الحصول على الأرقام المفقودة من بين مجموعة أرقام

الحصول على الأرقام المفقودة من بين مجموعة أرقام 

السلام عليكم و رحمة الله و بركاته 

 

 


موضوع اليوم هو سؤال لأحد الأخوة في منتدى 

Excel4Us 

على هذا الرابط 

من هنا

 

و فكرة السؤال حول إمكانية الحصول على الارقام المفقودة من ضمت تسلسل فواتير, كما هو في الصورة التالية :

فكما نلاحظ أن بعض أرقام الفواتير في السلسلة أعلاه غير موجود مثل الرقم 4 و 6 و 7 و غيرها من الأرقام 

و السؤال كيف يمكنني معرفة هذه الأرقام المفقودة

الحل يكون بإستخدام معادلات الصفيف array formula 

حيث سنستخدم معادلة تقوم بإسترجاع الرقم المفقود, و لبناء هذه المعادلة نحتاج لمعرفة الحد الأعلى و الحد الأدنى للقيم و من ثم بناء سلسلة رقمية بينهم و مقارنتها مع الأرقام الموجودة في النطاق أعلاه و هو من : 

A2:A11

و لعمل ذلك في الخلية C2 سنقوم بكتابة المعادلة التالية : 


ثم نقوم بالضغط على Ctrl+Shift+Enter

و لفهم آلية عملها سنقوم بتجزئتها لعدة أجزاء: 

فمعادلة min تقوم بإيجاد الحد الأدنى من السلسلة الرقمية  

ومعادلة max تقوم بإيجاد الحد الأعلى من السلسلة الرقمية 

و لبناء سلسلة رقمية بينهما نستخدم معادلتي 

row 

indirect

ثم بإستخدام معادلة match لمطابقة أرقام المصفوفة و أرقام النطاق الذي إخترناه 

A2:A11

سيووم الإكسيل بإعطائنا تسلسل الأرقام حسب ما هي في النطاق أعلاه, و لكن ما يهمنا هنا هو الارقام التي لا تملك تسلسل و التي ستعطي خطأ 

#N/A

في المعادلة 

و هي ما يحقق طلبنا, لذلك نستخدم معادلة ISNA للحصول عليها 

و عند هذه النقطة نكون قد حصلنا على مصفوفة جديدة بالأرقام المفقودة و لترتيبها و إعطائها أرقام نقوم بربطها من خلال معادلة IF مع سلسلة رقمية جديدة تعتمد على بناء تسلسل رقمي من 1 إلى أكبر قيمة

و من ثم يتم ربط المتغيرات بالمعادلة الأخيرة Small للحصول على الارقام بتسلسل ثابت 

و هي المرحلة الأخيرة المرتبطة بالمعادلة index 


كما هو في العامود C 



و بذلك تكون القضية قد تم حلها بشكل كامل 


لتحميل ملف الإكسيل و به المعادلات من خلال 

الرابط


أتمنى أن يستفيد الجميع منها 


دمتم في حفظ الله 


يحيى حسين 

Excel MVP 


 

اترك تعليقك

الاسم
:

البريد الإلكتروني
:


رقم الهاتف
:


التعليق
:

التعليقات